package com.loyi.cloud.hbsystem.datashare.service;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.vfs2.FileObject;
import org.apache.commons.vfs2.FileSystemException;
import org.apache.commons.vfs2.VFS;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.loyi.cloud.hbmis.api.IQueryService;

public class GISAnalyserExportTask implements Runnable {

	private IQueryService queryService;
	private int type;
	private int scope;

	private String title;// 主标题
	private String timepattern;// 副标题时间格式化格式
	private String vfspath;
	private List<Map<String, Object>> polls;

	public List<Map<String, Object>> getPolls() {
		return polls;
	}

	public void setPolls(List<Map<String, Object>> polls) {
		this.polls = polls;
	}

	public String getVfspath() {
		return vfspath;
	}

	public void setVfspath(String vfspath) {
		this.vfspath = vfspath;
	}

	public String getTimepattern() {
		return timepattern;
	}

	public void setTimepattern(String timepattern) {
		this.timepattern = timepattern;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public int getScope() {
		return scope;
	}

	public void setScope(int scope) {
		this.scope = scope;
	}

	public void setQueryService(IQueryService queryService) {
		this.queryService = queryService;
	}

	public IQueryService getQueryService() {
		return queryService;
	}

	public int getType() {
		return type;
	}

	public void setType(int type) {
		this.type = type;
	}

	private String pollcodes = "4402,4467,4486,4404,4D71,448A";
	private Logger logger = LoggerFactory.getLogger(this.getClass());

	public void run() {
		Date datatime;
		Calendar calendar = Calendar.getInstance();
		calendar.set(Calendar.MINUTE, 0);
		calendar.set(Calendar.SECOND, 0);
		calendar.set(Calendar.MILLISECOND, 0);
		if (type == 1) {
			calendar.set(Calendar.HOUR_OF_DAY, 0);
		}
		datatime = calendar.getTime();
		execute(datatime);
	}

	public void execute(Date datatime) {
		
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(datatime);
		calendar.set(Calendar.MINUTE, 0);
		calendar.set(Calendar.SECOND, 0);
		calendar.set(Calendar.MILLISECOND, 0);
		if (type == 1) {
			calendar.set(Calendar.HOUR_OF_DAY, 0);
		}
		datatime = calendar.getTime();
		
		logger.info(String.format("查询%s小时污染物浓度数据", DateFormatUtils.format(datatime, "yyyy-MM-dd HH时")));
		List<Map<String, Object>> list, regions, aqi_list;
		long endtime, starttime = System.currentTimeMillis();
		Map<String, Object> dataRow = null,regionMap;
		FileObject fo = null;
		OutputStream os = null;
		Workbook wb = new HSSFWorkbook();
		Sheet s = wb.createSheet();
		Row r = null;
		Cell c = null;
		String sql, aqi_sql;
		String searchtime;
		String path;
		searchtime = DateFormatUtils.format(datatime, "yyyy-MM-dd HH:mm:ss");

		// sql = "select bb.name,bb.lat,bb.lng,bb.ptid,dd.name as city from
		// t_bas_air_relation aa left join t_bas_pointer bb on aa.ptid=bb.ptid
		// left join t_bas_pointer_region cc on aa.ptid=cc.ptid left join
		// t_cod_region dd on cc.region_lv3=dd.code where bb.name is not null
		// order by aa.ptid";
		
		regions = this.queryService.query("SELECT * FROM t_cod_region");
		
		
		

		sql = "SELECT p.ptid,r1.name as city,p.name,p.lat,p.lng,t.aqi,t.maxpoll" + ",t1.value AS '448A',t1.iaqi AS '448A_iaqi'"
				+ ",t2.value AS '4486',t2.iaqi AS '4486_iaqi'"
				+ ",t3.value AS '4402',t3.iaqi AS '4402_iaqi'"
				+ ",t4.value AS '4404',t4.iaqi AS '4404_iaqi'"
				+ ",t5.value AS '4467',t5.iaqi AS '4467_iaqi'"
				+ ",t6.value AS '4D71',t6.iaqi AS '4D71_iaqi'" + "FROM t_bas_pointer AS p"
				+ " LEFT JOIN t_bas_pointer_region AS r ON r.ptid=p.ptid"
				+ " LEFT JOIN t_cod_region AS r1 ON r1.code=r.region_lv3"
				+ " LEFT JOIN t_bas_air_total AS t ON t.targetid=p.ptid AND t.datatime='%s' AND t.type=%s AND t.targettype=0"
				+ " LEFT JOIN t_bas_air_total_detail AS t1 ON t1.type=t.type AND t1.datatime=t.datatime AND t1.targettype=t.targettype AND t1.targetid=t.targetid AND t1.pollcode='448A' AND t1.scope=%s"
				+ " LEFT JOIN t_bas_air_total_detail AS t2 ON t2.type=t.type AND t2.datatime=t.datatime AND t2.targettype=t.targettype AND t2.targetid=t.targetid AND t2.pollcode='4486' AND t2.scope=%s"
				+ " LEFT JOIN t_bas_air_total_detail AS t3 ON t3.type=t.type AND t3.datatime=t.datatime AND t3.targettype=t.targettype AND t3.targetid=t.targetid AND t3.pollcode='4402' AND t3.scope=%s"
				+ " LEFT JOIN t_bas_air_total_detail AS t4 ON t4.type=t.type AND t4.datatime=t.datatime AND t4.targettype=t.targettype AND t4.targetid=t.targetid AND t4.pollcode='4404' AND t4.scope=%s"
				+ " LEFT JOIN t_bas_air_total_detail AS t5 ON t5.type=t.type AND t5.datatime=t.datatime AND t5.targettype=t.targettype AND t5.targetid=t.targetid AND t5.pollcode='4467' AND t5.scope=%s"
				+ " LEFT JOIN t_bas_air_total_detail AS t6 ON t6.type=t.type AND t6.datatime=t.datatime AND t6.targettype=t.targettype AND t6.targetid=t.targetid AND t6.pollcode='4D71' AND t6.scope=%s"
				+ " WHERE r.subjection IN(1,2,3,4) ORDER BY p.ptid";
		sql = String.format(sql, searchtime, type, scope, scope, scope, scope, scope, type == 1 ? 17 : scope);
		//logger.debug("sql=" + sql);
		list = queryService.query(sql);
		if (list != null && list.size() > 0) {
			logger.info(String.format("查询成功，共有%s个监测数据", list.size()));
			logger.info("开始将Excel保存到VFS上传目录");
		} else if (polls == null || polls.size() < 1) {
			logger.error("污染因子参数配置错误！");
			return;
		} else {
			logger.error("无相关数据");
			return;
		}
 
		for (int i = 0; i < list.size() + 3; i++) {
			r = s.createRow(i);
			for (int j = 0; j < polls.size() + 4; j++) {
				c = r.createCell(j);

				if (i == 0 && j == 0) {
					c.setCellValue(title);
					break;
				} else if (i == 1 && j == 0) {
					c.setCellValue(DateFormatUtils.format(datatime, timepattern));
					break;
				} else {
					if (i > 2) {
						dataRow = list.get(i - 3);
					} else {
						dataRow = null;
					}
					switch (j) {
					case 0:
						if (dataRow == null) {
							c.setCellValue("city");
						} else {
							c.setCellValue(String.valueOf(dataRow.get("city")));
						}
						break;
					case 1:
						if (dataRow == null) {
							c.setCellValue("name");
						} else {
							c.setCellValue(dataRow.get("name").toString());
						}
						break;
					case 2:
						if (dataRow == null) {
							c.setCellValue("lon");
						} else {
							c.setCellValue(Double.parseDouble(dataRow.get("lng").toString()));
						}
						break;
					case 3:
						if (dataRow == null) {
							c.setCellValue("lat");
						} else {
							c.setCellValue(Double.parseDouble(dataRow.get("lat").toString()));
						}
						break;
					default:
						String pollcode = polls.get(j - 4).get("pollcode").toString();
						int unit = Integer.parseInt(polls.get(j - 4).get("unit").toString());
						if (dataRow == null) {
							c.setCellValue(polls.get(j - 4).get("name").toString());
						} else {
							if (dataRow.containsKey(pollcode) && dataRow.get(pollcode)!=null) {
								c.setCellValue(
										NumberUtils.toDouble(dataRow.get(pollcode).toString(), 0) * unit);
							} else {
								c.setCellValue(0);
							}

						}
						break;
					}
				}
			}
		}
		try {
			if (type == 1) {
				path = String.format("%s/%s/%s_000%s.xls", vfspath, "day",
						DateFormatUtils.format(datatime, "yyyy/MM/yyyyMMddHHmmss"), type);
			} else {
				path = String.format("%s/%s/%s_000%s.xls", vfspath, "hour",
						DateFormatUtils.format(datatime, "yyyy/MM/dd/yyyyMMddHHmmss"), type);
			}
			System.out.println(path);
			fo = VFS.getManager().resolveFile(path);
			os = fo.getContent().getOutputStream();
			wb.write(os);
			endtime = System.currentTimeMillis();
			logger.info(String.format("上传成功,耗时%sms", endtime - starttime));
		} catch (IOException e) {
			logger.error("创建VFS文件目录失败，请检查权限设置或者路径是否正确");
		} finally {
			// 关闭输出流
			try {
				if (os != null)
					os.close();
				if (fo != null)
					fo.close();
			} catch (FileSystemException e) {
				// e.printStackTrace();
			} catch (IOException e) {
				// e.printStackTrace();
			}
		}
	}

}
